package com.gugu.procedure.util;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.gugu.procedure.exceptions.BusinessException;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

@Slf4j
public class ExcelUtil {

    public static void main(String[] arg) {


    }

    /**
     * 下载
     *
     * @param response web响应
     * @param datas    导出数据
     * @param clazz    实体类
     * @param fileName 导出文件名称
     * @return
     */
    public static void download(HttpServletResponse response, HttpServletRequest request, List datas, Class clazz, String fileName) {
        // parseHead（获取实体类定义的属性名称，也就是excel表头）
        download(response, request, datas, clazz, parseHead(clazz), fileName);
    }

    /**
     * 下载
     *
     * @param response web响应
     * @param datas    导出数据
     * @param head     表头
     * @param fileName 导出文件名称
     * @return
     */
    public static void download(HttpServletResponse response, HttpServletRequest request, List datas, Class clazz, List <List <String>> head, String fileName) {
        // 设置web响应输出的文件名称
        setResponseHeader(response, request, fileName);
        try {
            // 核心中的核心
            export(response.getOutputStream(), head, datas, clazz);
        } catch (IOException e) {
            log.info("无法获取响应流", e);
        }
    }

    /**
     * 设置web响应输出的文件名称
     *
     * @param response web响应
     * @param fileName 导出文件名称
     */
    private static void setResponseHeader(HttpServletResponse response, HttpServletRequest request, String fileName) {
        response.reset();
        response.setContentType("application/x-download");
        response.setHeader("Access-Control-Allow-Origin", request.getHeader("origin"));
        response.setHeader("Access-Control-Allow-Credentials", "true");
        try {
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            log.info("不支持的编码", e);
        }
        response.setCharacterEncoding("UTF-8");
    }

    /**
     * 解析实体类
     * cd D:\software\jar
     *
     * @param clazz 实体类
     * @return
     */
    private static List <List <String>> parseHead(Class clazz) {
        Field[] fields = clazz.getDeclaredFields();
        List <List <String>> heads = new ArrayList <>();
        for (Field field : fields) {
            List <String> head = new ArrayList <>();
            // 在开发我们应该少不了swagger, 这个注解是swagger提供的，当然我们也可以自定义一个注解。（作用是为了属性名映射中文名称输出到excel表头）
            ApiModelProperty apiAnnotation = field.getAnnotation(ApiModelProperty.class);
            if (apiAnnotation == null) {
                continue;
            }
            head.add(apiAnnotation.value());
            heads.add(head);
        }
        return heads;
    }

    /**
     * @param os    字节输出流
     * @param head  表头
     * @param datas 导出数据
     * @param clazz 实体类
     * @throws IOException
     */
    private static void export(OutputStream os, List <List <String>> head, List datas, Class clazz) throws IOException {
        if (datas == null) {
            datas = new ArrayList();
        }
        com.alibaba.excel.ExcelWriter excelWriter = null;
        BufferedOutputStream bos = new BufferedOutputStream(os);
        try {
            excelWriter = EasyExcel.write(bos, clazz).build();
            WriteSheet testSheet = EasyExcel.writerSheet("sheet1")
                    .head(head)
                    .build();
            excelWriter.write(datas, testSheet);
        } catch (Exception e) {
            log.info("easyexcel初始化错误", e);
        } finally {
            bos.flush();
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    //将两个excel合并两个sheet
    //读取excel
    public static Workbook readExcel(File file) {
        Workbook wb = null;
        if (file == null) {
            return null;
        }
        String extString = file.getName().toLowerCase();
        InputStream is = null;
        try {
            is = new FileInputStream(file);
            if (extString.contains(".xlsx")) {
                return wb = new XSSFWorkbook(is);
            } else if (extString.contains(".xls")) {
                return wb = new HSSFWorkbook(is);
            } else {
                return wb = null;
            }

        } catch (Exception e) {
            throw new BusinessException("文件格式异常，请调整文件格式");
        }
    }


    //获取单元格各类型值，返回字符串类型
    public static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        cell.setCellType(CellType.STRING);
        cellValue = cell.getStringCellValue();
        cellValue = cellValue.replaceAll("\n", "");
        cellValue = cellValue.replaceAll(" ", "");
        return cellValue.trim();
    }


    /**
     * #复制sheet到新的excel文件中
     *
     * @param workbook      excel工作簿
     * @param tmpSheet      来源sheet
     * @param newExcelSheet 新生成的sheet
     */
    public static void copyExcelSheet(XSSFWorkbook workbook, XSSFSheet tmpSheet, XSSFSheet newExcelSheet) {
        // 合并单元格
        mergeSheetAllRegion(tmpSheet, newExcelSheet);
        // 设置单元格列宽度
        // 获取最后一个单元格位置
        int len = tmpSheet.getRow(tmpSheet.getFirstRowNum()).getLastCellNum();
        for (int i = 0; i < len; i++) {
            newExcelSheet.setColumnWidth(i, tmpSheet.getColumnWidth(i));
        }
        // 复制每行内容
        Iterator <Row> it = tmpSheet.iterator();
        while (it.hasNext()) {
            XSSFRow tmpRow = (XSSFRow) it.next();
            // 创建新行
            XSSFRow newExcelRow = newExcelSheet.createRow(tmpRow.getRowNum());
            // 复制行
            copyExcelRow(workbook, tmpRow, newExcelRow);
        }
    }

    /**
     * #合并单元格
     *
     * @param tmpSheet      来源sheet
     * @param newExcelSheet 目标sheet
     */
    private static void mergeSheetAllRegion(XSSFSheet tmpSheet, XSSFSheet newExcelSheet) {
        int num = tmpSheet.getNumMergedRegions();
        CellRangeAddress cellRange = null;
        for (int i = 0; i < num; i++) {
            cellRange = tmpSheet.getMergedRegion(i);
            newExcelSheet.addMergedRegion(cellRange);
        }
    }

    /**
     * #复制excel中的行到新的sheet中
     *
     * @param workbook    目标工作簿
     * @param tmpRow      来源excel行
     * @param newExcelRow 目标excel行
     */
    public static void copyExcelRow(XSSFWorkbook workbook, XSSFRow tmpRow, XSSFRow newExcelRow) {
        // 设置行高
        newExcelRow.setHeight(tmpRow.getHeight());
        // 获取所有列
        Iterator <Cell> it = tmpRow.cellIterator();
        while (it.hasNext()) {
            XSSFCell tmpCell = (XSSFCell) it.next();
            // 创建单元格
            XSSFCell newExcelCell = newExcelRow.createCell(tmpCell.getColumnIndex());
            // 复制单元格
            copyExcelCell(workbook, tmpCell, newExcelCell);
        }
    }

    /**
     * #复制单元格
     *
     * @param workbook     目标工作簿
     * @param tmpCell      来源excel单元格
     * @param newExcelCell 目标excel单元格
     */
    public static void copyExcelCell(XSSFWorkbook workbook, XSSFCell tmpCell, XSSFCell newExcelCell) {
        XSSFCellStyle newExcelStyle = workbook.createCellStyle();
        // 复制单元格样式
        newExcelStyle.cloneStyleFrom(tmpCell.getCellStyle());
        // 单元格样式
        newExcelCell.setCellStyle(newExcelStyle);
        if (tmpCell.getCellComment() != null) {
            newExcelCell.setCellComment(tmpCell.getCellComment());
        }
        // 不同数据类型处理
        CellType tmpCellType = tmpCell.getCellTypeEnum();
        newExcelCell.setCellType(tmpCellType);
        if (tmpCellType == CellType.NUMERIC) {
            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(tmpCell)) {
                newExcelCell.setCellValue(tmpCell.getDateCellValue());
            } else {
                newExcelCell.setCellValue(tmpCell.getNumericCellValue());
            }
        } else if (tmpCellType == CellType.STRING) {
            newExcelCell.setCellValue(tmpCell.getRichStringCellValue());
        } else if (tmpCellType == CellType.BLANK) {
        } else if (tmpCellType == CellType.BOOLEAN) {
            newExcelCell.setCellValue(tmpCell.getBooleanCellValue());
        } else if (tmpCellType == CellType.ERROR) {
            newExcelCell.setCellErrorValue(tmpCell.getErrorCellValue());
        } else if (tmpCellType == CellType.FORMULA) {
            newExcelCell.setCellFormula(tmpCell.getCellFormula());
        } else {
        }
    }

}
